package jdly.water.api.dao.report;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import jdly.water.entity.base.Meter;

public interface IndexDao extends JpaRepository<Meter, String> {

	/**
	 * 首页基本信息情况
	 * 
	 * @return
	 */
	@Query(value = "SELECT  NVL(COUNT(1),0) AS TOTAL FROM METER UNION ALL SELECT  NVL(COUNT(1),0) FROM BUILDING_METER M  WHERE M.METER_MODEL ='栋表' UNION ALL SELECT  NVL(COUNT(1),0) FROM BUILDING_METER M WHERE M.METER_MODEL ='总表' UNION ALL SELECT  NVL(COUNT(1),0) FROM COLLECTOR", nativeQuery = true)
	public List baseInfo();

	/**
	 * 首页上传情况
	 * 
	 * @return
	 */
	@Query(value = "SELECT NVL(COUNT(1),0) FROM COLLECTOR C WHERE C.DTU_STATEMENT = 1 UNION ALL  SELECT NVL(COUNT(1),0) FROM READ_DATA_MONITOR UNION ALL SELECT NVL(COUNT(1),0) FROM READ_DATA_BM_MONITOR BMD,BUILDING_METER BM WHERE BM.ID =BMD.METER_ID AND BM.METER_MODEL ='栋表' UNION ALL SELECT NVL(COUNT(1),0) FROM READ_DATA_BM_MONITOR BMD ,BUILDING_METER BM WHERE BM.ID =BMD.METER_ID AND BM.METER_MODEL ='总表'", nativeQuery = true)
	public List uploadInfo();

	@Query(value = "SELECT NVL(SUM(ACCOUNT_MONEY),0) TOTAL FROM USER_ACCOUNT UNION ALL SELECT NVL(SUM(BALANCE),0) FROM USER_ACCOUNT C WHERE C.BALANCE <0 UNION ALL SELECT NVL(SUM(TOTALFLOW),0) FROM USER_ACCOUNT ", nativeQuery = true)
	public List cost();
}
